package com.ch.test.dao;

import java.sql.Connection;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ch.base.db.DBBean;
import com.ch.base.util.StringUtils;
import com.ch.base.obj.QueryResultObj;
import com.ch.base.obj.QuerySettingObj;

/**
 * Class Name : CmsOfflineContentsDAO
 * Description: 
 * Copyright  : Copyright (c) 2013
 * @version 1.0
 */
public class CmsOfflineContentsDAO extends DBBean {

	public CmsOfflineContentsDAO(Connection con){
		super(con);
		this.setLogger(this.getClass().getName());
	}

	/**
	 * 新增 insert(CmsOfflineContentsWrapper qra)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表新增成功
	 * @param CmsOfflineContentsWrapper
	 * @return
	 * @throws Exception 
	 */
	public int insert(CmsOfflineContentsWrapper wra) throws Exception{
		int result = -1;
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("insert into CMS_OFFLINE_CONTENTS ( ");
		sbSql.append(" RESOURCE_ID, ");
		sbSql.append(" FILE_CONTENT) ");
		sbSql.append("values (?, ?);");

		int count = 0;
		Object[] params = new Object[2];
		params[count++] = wra.getResourceId();
		params[count++] = wra.getFileContent();

		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(sbSql.toString(), params);
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;	
	}

	/**
	 * 修改 updateByPK(CmsOfflineContentsWrapper wra, String resourceId)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表修改成功
	 * @param CmsOfflineContentsWrapper
	 * @param resourceId	
	 * @return
	 * @throws Exception 
	 */
	public int updateByPK(CmsOfflineContentsWrapper wra, String resourceId) throws Exception{
		int result = -1 ;
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		sbSql.append("update CMS_OFFLINE_CONTENTS set ");
		if( wra.getResourceId() != null ) { sbSql.append(" RESOURCE_ID = ?, "); al.add( wra.getResourceId() ); }
		if( wra.getFileContent() != null ) { sbSql.append(" FILE_CONTENT = ?, "); al.add( wra.getFileContent() ); }

		sbSql.delete(sbSql.toString().lastIndexOf(","), sbSql.toString().length());

		sbSql.append(" where 1 = 1 ");
		sbSql.append("   and resourceId = ? ");
		al.add(resourceId); 

		Object[] params = al.toArray();

		try{
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(sbSql.toString(), params);
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;
	}

	/**
	 * 修改 update(CmsOfflineContentsWrapper wra, String whereClause, Object[] whereArgs)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表修改成功
	 * 	(2)	whereClause 為修改時的限定條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 " and " 做為啟始字串。
	 * @param CmsOfflineContentsWrapper
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 * @throws Exception 
	 */
	public int update(CmsOfflineContentsWrapper wra, String whereClause, Object[] whereArgs)throws Exception{
		int result = -1 ;
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		sbSql.append("update CMS_OFFLINE_CONTENTS set ");
		if( wra.getResourceId() != null ) { sbSql.append(" RESOURCE_ID = ?, "); al.add( wra.getResourceId() ); }
		if( wra.getFileContent() != null ) { sbSql.append(" FILE_CONTENT = ?, "); al.add( wra.getFileContent() ); }

		sbSql.delete(sbSql.toString().lastIndexOf(","), sbSql.toString().length());

		sbSql.append(" where 1 = 1 ");
		sbSql.append(whereClause);
		if(whereArgs != null && whereArgs.length > 0){
			for(int i = 0; i < whereArgs.length; i++){
				al.add( whereArgs[i] );
			}
		}

		Object[] params = al.toArray();

		try{
			if(StringUtils.isEmpty(whereClause)){
				throw new Exception("因未輸入 where 條件式，避免異動到所有資料，故強制關閉此update指令。");
			}
			log.info("update(): " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(sbSql.toString(), params);
		} catch (Exception e) {
			log.error("SQL in " + this.getClass().getSimpleName() + " update(): " + this.getPreparedSQL(sbSql.toString(), params) + " executed failed.");
			throw e;
		}

		return result;
	}

	/**
	 * 刪除 deleteByPK(String resourceId)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表刪除成功。
	 * @param resourceId	
	 * @return
	 * @throws Exception
	 */
	public int deleteByPK(String resourceId) throws Exception{
		int result = -1;
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("delete from CMS_OFFLINE_CONTENTS "); 
		sbSql.append(" where 1 = 1 ");
		sbSql.append("   and RESOURCE_ID = ? ");

		int count = 0;
		Object[] params = new Object[1];
		params[count++] = resourceId;

		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(sbSql.toString(), params);
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;
	}

	/**
	 * 刪除 delete(CmsOfflineContentsWrapper wra, String whereClause, Object[] whereArgs)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表刪除成功。
	 * 	(2)	whereClause 為不在 wrapper 傳入欄位的限制條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 " and " 做為啟始字串。
	 * @param CmsOfflineContentsWrapper
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 * @throws Exception
	 */
	public int delete(CmsOfflineContentsWrapper wra, String whereClause, Object[] whereArgs) throws Exception{
		int result = -1;
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		sbSql.append("delete CMS_OFFLINE_CONTENTS ");
		sbSql.append(" where 1 = 1 ");

		if (wra != null) {
			if( wra.getResourceId() != null ) { sbSql.append(" and RESOURCE_ID like ? "); al.add( wra.getResourceId() ); }
			if( wra.getFileContent() != null ) { sbSql.append(" and FILE_CONTENT like ? "); al.add( wra.getFileContent() ); }
		}

		sbSql.append(whereClause);
		if(whereArgs != null && whereArgs.length > 0){
			for(int i = 0; i < whereArgs.length; i++){
				al.add( whereArgs[i] );
			}
		}

		Object[] params = al.toArray();

		try {
			if(wra == null && StringUtils.isEmpty(whereClause)){
				throw new Exception("因未輸入 where 條件式，避免異動到所有資料，故強制關閉此delete指令。");
			}
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(sbSql.toString(), params);
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;	
	}

	/**
	 * 查詢 queryByPK(String resourceId)：
	 * 	(1)	此method 回傳之型態為單一 wrapper。
	 * @param resourceId	
	 * @return CmsOfflineContentsWrapper
	 * @throws Exception
	 */
	public CmsOfflineContentsWrapper queryByPK(String resourceId) throws Exception{
		CmsOfflineContentsWrapper result = new CmsOfflineContentsWrapper();
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select * "); 
		sbSql.append("  from CMS_OFFLINE_CONTENTS ");
		sbSql.append(" where 1 = 1 ");
		sbSql.append("   and RESOURCE_ID = ? ");

		int count = 0;
		Object[] params = new Object[1];
		params[count++] = resourceId;

		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			List<CmsOfflineContentsWrapper> list = ((QueryResultObj) this.executeQuery(CmsOfflineContentsWrapper.class, sbSql.toString(), params)).getQueryList();

			if( ( list != null ) && ( list.size() > 0 ) ){
				result = (CmsOfflineContentsWrapper) list.get(0);
			}
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;
	}

	/**
	 * 查詢 query(String[] columns, CmsOfflineContentsWrapper wra, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit, String offset)：
	 * 	(1)	此method 回傳之型態為List<Wrapper>
	 * 	(2)	whereClause 為不在 wrapper 傳入欄位的查詢條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 " and " 做為啟始字串。
	 * @param CmsOfflineContentsWrapper
	 * @param columns
	 * @param whereClause
	 * @param whereArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @param limit
	 * @param offset
	 * @return QueryResultObj
	 * @throws Exception
	 */
	public QueryResultObj query(String[] columns, CmsOfflineContentsWrapper wra, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit, String offset) throws Exception{
		QueryResultObj queryResultObj = new QueryResultObj();
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		sbSql.append("select "); 
		if (columns != null && columns.length > 0) {
			for (int i = 0; i < columns.length; i++) {
				if (i < columns.length - 1) {
					sbSql.append( columns[i] + ", ");
				}else{
					sbSql.append( columns[i] );
				}
			}
		} else {
			sbSql.append(" * "); 
		}
		sbSql.append("  from CMS_OFFLINE_CONTENTS ");
		sbSql.append(" where 1 = 1 ");

		if (wra != null) {
			if( wra.getResourceId() != null )   { sbSql.append(" and RESOURCE_ID like ? "); al.add( wra.getResourceId() ); }
			if( wra.getFileContent() != null )   { sbSql.append(" and FILE_CONTENT like ? "); al.add( wra.getFileContent() ); }
		}

		if (StringUtils.isNotEmpty(whereClause)) {
			sbSql.append(whereClause);
		}

		if(whereArgs != null && whereArgs.length > 0){
			for(int i=0; i < whereArgs.length; i++){
				al.add( whereArgs[i] );
			}
		}

		if (StringUtils.isEmpty(orderBy)) {
			/*
			 *	若無傳入orderBy之數值, 則以預設值(PK值)取代
			 */
			orderBy = "RESOURCE_ID";
		}

		QuerySettingObj querySettingObj = new QuerySettingObj();
		querySettingObj.setSql(sbSql.toString());
		querySettingObj.setPreparedStatementParams(al.toArray());
		querySettingObj.setGroupBy(groupBy);
		querySettingObj.setHaving(having);
		querySettingObj.setOrderBy(orderBy);
		querySettingObj.setLimit(limit);
		querySettingObj.setOffset(offset);

		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(querySettingObj));
			if (columns != null && columns.length > 0) {
				querySettingObj.setResultType(Map.class);
			} else {
				querySettingObj.setResultType(CmsOfflineContentsWrapper.class);
			}
			queryResultObj = this.executeQuery(querySettingObj);
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(querySettingObj));
			throw e;
		}

		return queryResultObj;
	}
}

